﻿CREATE TABLE [dbo].[hrm_Departments] (
    [DepartmentID]      INT            IDENTITY (1, 1) NOT NULL,
    [DepartmentCode]    NVARCHAR (50)  NULL,
    [DepartmentName]    NVARCHAR (150) NULL,
    [DepartmentAddress] NVARCHAR (450) NULL,
    [ParentID]          INT            NULL,
    [KeyCode]           VARCHAR (900)  NULL,
    CONSTRAINT [PK_hrm_Departments] PRIMARY KEY CLUSTERED ([DepartmentID] ASC),
    CONSTRAINT [FK_hrm_Departments_hrm_Departments] FOREIGN KEY ([ParentID]) REFERENCES [dbo].[hrm_Departments] ([DepartmentID])
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_hrm_Departments]
    ON [dbo].[hrm_Departments]([DepartmentCode] ASC);


GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER dbo.DepartmentInsert
   ON  dbo.hrm_Departments
   AFTER  INSERT,update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		DECLARE @PARENT_KEY_COCDE VARCHAR(900)
		DECLARE @ID INT
		SELECT @ID=PARENTID FROM INSERTED  
		SELECT @PARENT_KEY_COCDE =KeyCode FROM hrm_Departments WHERE DEPARTMENTID=@ID
		IF(ISNULL(@PARENT_KEY_COCDE,'')='')
		BEGIN
		SELECT @ID=DepartmentID FROM INSERTED  
		update hrm_Departments set KeyCode='.'+cast(DepartmentID as varchar(40))+'.' WHERE DEPARTMENTID=@ID
		END
		ELSE
		BEGIN
		SELECT @ID=DepartmentID FROM INSERTED  
		update hrm_Departments set KeyCode=@PARENT_KEY_COCDE+cast(DepartmentID as varchar(40))+'.'
		WHERE DEPARTMENTID=@ID
		END
    -- Insert statements for trigger here

END